Imports System.Collections
Imports System.Data.OracleClient

Public Class clsGnrtProCBodyCG

    Dim mStrScriptCode As String = ""
    Dim mCmdData As OracleCommand
    Dim mDrData As OracleDataReader
    Dim mDrStringIndc As OracleDataReader
    Dim mStrTableName As String = ""
  Dim clsobjFileGen As clsGnrtFile

    Public Function mFnGenerateProCBodyCG(ByVal aStrTableName As String _
                                      ) As Boolean
        Dim lStrTableName As String
        Dim lStrCode As String = ""
        Dim lStrColName As String = ""
        Dim lStrTempColName As String = ""
        Dim lStrColDataType As String = ""
        Dim lStrVrblSuffix As String = ""
        Dim lStrColNulInd As String
        Dim lStrColPrm As String
        Dim lIntCNT As Integer
        Dim lIntTtlCNT As Integer

        Dim lStrPrnParm As String = ""
        Dim lStrPrnLst As String = "%s"
        Dim lStrColLst As String = ""
        Dim lStrTemCol As String = ""
        Dim lStrWhereLst As String = ""
        Dim lIntColCount As Integer = 0

        Dim lIntIndex As Integer

        Dim lStrTemp As String = ""
    Dim lStrTemp2 As String = ""
    Dim lStrTmpStrucName As String
    Dim lStrTmpTableName As String

        If mFnCheckTableValidity(aStrTableName) = False Then
            MessageBox.Show("Table doesnot exist", gCONST_MSG_HEADER)
            Exit Function
        End If

        mStrTableName = aStrTableName

    lStrTmpTableName = aStrTableName.Substring((aStrTableName.IndexOf("_") + 1)) + "_DB"

    lStrTableName = aStrTableName.Substring((aStrTableName.IndexOf("_") + 1))
    lStrTableName = lStrTableName.Replace("_", " ")
        lStrTableName = StrConv(lStrTableName, VbStrConv.ProperCase)
        lStrTableName = lStrTableName.Replace(" ", "")

    lStrTmpStrucName = lStrTableName + "DB"

    clsobjFileGen = New clsGnrtFile()

        '---------------------------------------------------------------------------------------------------------------
        'Gen Header
        '---------------------------------------------------------------------------------------------------------------

        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*              H E A D E R  I N F O R M A T I O N  B O X                */" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*                    TCS CONFIDENTIAL INFORMATION                       */" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*              COPYRIGHT (c) 2007 TCS All Rights Reserved               */" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*  This module contains confidential and proprietary information of     */" + vbCrLf
        lStrCode = lStrCode + "/*  TCS, and any reproduction, disclosure or use in whole or in part     */" + vbCrLf
        lStrCode = lStrCode + "/*  is expressly forbidden except as may be specifically authorized      */" + vbCrLf
        lStrCode = lStrCode + "/*  by prior written agreement or permission of TCS.                     */" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf
        lStrCode = lStrCode + "/*   Author    : Code Generator                                          */" + vbCrLf
        lStrCode = lStrCode + "/*   Date      : " + Now.Date + Space(56 - CStr(Now.Date).Length) + "*/" + vbCrLf
        lStrCode = lStrCode + "/*   Synopsis  : This File contains the basic dml services based on      */" + vbCrLf
        lStrCode = lStrCode + "/*               primary key for " + mStrTableName + Space(40 - mStrTableName.Length) + "*/" + vbCrLf
        lStrCode = lStrCode + "/*                                                                       */" + vbCrLf
        lStrCode = lStrCode + "/*   Modifications : This code is generated and should not be modified   */" + vbCrLf
        lStrCode = lStrCode + "/*                   manually                                            */" + vbCrLf
        lStrCode = lStrCode + "/*************************************************************************/" + vbCrLf + vbCrLf

    lStrCode = lStrCode + "#include <" + lStrTmpStrucName + ".h>" + vbCrLf

        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "EXEC SQL INCLUDE sqlca.h;" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf


        mprGetTableCols()
        lIntTtlCNT = 0
        lStrWhereLst = ""
        While mDrData.Read
            lIntTtlCNT = lIntTtlCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")

            If mDrData.IsDBNull(4) Then
                lStrColPrm = ""
            Else
                lStrColPrm = mDrData("TTL_INDX_TYPE")
            End If

            If Trim(lStrColPrm) = "P" Then
                lIntColCount = lIntColCount + 1   'Primary Key Columns 
                If lIntColCount = 1 Then
                    lStrWhereLst = lStrWhereLst + "      strcat(caQueryString,""  WHERE " + lStrColName + "=:n" + lIntTtlCNT.ToString.Trim + """);" + vbCrLf
                Else
                    lStrWhereLst = lStrWhereLst + "      strcat(caQueryString,""    AND " + lStrColName + "=:n" + lIntTtlCNT.ToString.Trim + """);" + vbCrLf
                End If
            End If
        End While

        '1-------------------------------------------------------------------------------------------------
        '   Gen Insert Part
        '1-------------------------------------------------------------------------------------------------
    mprGenPartHead("Insrt", lStrTmpStrucName, lStrCode)

        If lStrTemp.Length > 70 Then
      lStrCode = lStrCode + "RETURN_VALUE Insrt" + lStrTmpStrucName + "DY" + "(" + vbCrLf + "BOOL bPrpre, " + lStrTmpTableName + vbCrLf + "   * p" + lStrTmpStrucName + " )" + vbCrLf
        Else
      lStrCode = lStrCode + "RETURN_VALUE Insrt" + lStrTmpStrucName + "DY" + "(" + vbCrLf + "BOOL bPrpre," + lStrTmpTableName + " * p" + lStrTmpStrucName + " )" + vbCrLf
        End If

    mprGenPartInclude("Insrt", lStrTmpStrucName, lStrCode)

        lStrCode = lStrCode + "      strcpy(caQueryString,"" INSERT INTO "");" + vbCrLf
        lStrCode = lStrCode + "      strcat(caQueryString,"" TableName[" + mStrTableName + "-1]"");" + vbCrLf

        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")

            If lIntCNT = 1 Then
                lStrCode = lStrCode + "      strcat(caQueryString,""( " + lStrColName + ","");" + vbCrLf
            Else
                If lIntCNT = lIntTtlCNT Then
                    lStrCode = lStrCode + "      strcat(caQueryString,""" + lStrColName + """);" + vbCrLf
                Else
                    lStrCode = lStrCode + "      strcat(caQueryString,""" + lStrColName + ","");" + vbCrLf
                End If

            End If

        End While                                               'above ok , need test, where has some problem
        lStrCode = lStrCode + "      strcat(caQueryString,"" VALUES "");" + vbCrLf
        lStrCode = lStrCode + "      strcat(caQueryString,"" ( "

        For lIntIndex = 1 To lIntTtlCNT
            lStrCode = lStrCode + ":n" + lIntIndex.ToString.Trim

            If lIntIndex = lIntTtlCNT Then
                lStrCode = lStrCode + ")"");" + vbCrLf
                Exit For
            Else
                lStrCode = lStrCode + ","
            End If

            If lIntIndex Mod 3 = 0 Then
                lStrCode = lStrCode + """);" + vbCrLf
                lStrCode = lStrCode + "      strcat(caQueryString,""  "
            End If
        Next

        lStrCode = lStrCode + "" + vbCrLf
    lStrCode = lStrCode + "      EXEC SQL PREPARE Insrt" + lStrTmpStrucName + "Query FROM :caQueryString;" + vbCrLf
        lStrCode = lStrCode + "      if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_TRC,Warn(sqlca.sqlcode),""%s""" + vbCrLf
    lStrCode = lStrCode + "          ,""Error in Prepare in Insrt" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "        ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""" + vbCrLf
    lStrCode = lStrCode + "        ,""Exiting Insrt" + lStrTmpStrucName + " after parsing"");" + vbCrLf
        lStrCode = lStrCode + "      #endif" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "    }" + vbCrLf

        lStrCode = lStrCode + "" + vbCrLf
    lStrCode = lStrCode + "  EXEC SQL EXECUTE Insrt" + lStrTmpStrucName + "Query USING " + vbCrLf


        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")
            lStrTempColName = lStrColName
            mprGetColName(lStrTempColName)

            lStrTempColName = lStrTempColName.Replace("_", " ")
            lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
            lStrTempColName = lStrTempColName.Replace(" ", "")

            If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                lStrTemp2 = ""
            Else
                lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
            End If

            mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)

            'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
            mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)

            lStrColNulInd = mDrData("TAB_COL_NULL_INDC")


      lStrTempColName = ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName

            If lIntCNT <> lIntTtlCNT Then
                lStrCode = lStrCode + "            " + lStrTempColName + "," + vbCrLf
            Else
                lStrCode = lStrCode + "            " + lStrTempColName + ";" + vbCrLf
            End If

        End While

        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "  if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "    {" + vbCrLf
        lStrCode = lStrCode + "      ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""," + vbCrLf
    lStrCode = lStrCode + "                      ""Error Executing Insrt" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "      return(sqlca.sqlcode) ;" + vbCrLf
        lStrCode = lStrCode + "    }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf


        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Insrt", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)
        '----------------------------------------------------------------------------------------------------------------
        'Insert End
        '----------------------------------------------------------------------------------------------------------------


        '2-------------------------------------------------------------------------------------------------
        '   Gen Update Part
        '1-------------------------------------------------------------------------------------------------
    mprGenPartHead("Updt", lStrTmpStrucName, lStrCode)

    lStrCode = lStrCode + "RETURN_VALUE Updt" + lStrTmpStrucName + "DY" + "(BOOL bPrpre," + vbCrLf + " " + lStrTmpTableName + " * p" + lStrTmpStrucName + ")" + vbCrLf

    mprGenPartInclude("Updt", lStrTmpStrucName, lStrCode)

        lStrCode = lStrCode + "      strcpy(caQueryString,"" UPDATE "");" + vbCrLf
        lStrCode = lStrCode + "      strcat(caQueryString,"" TableName[" + mStrTableName + "-1]"");" + vbCrLf
        lStrCode = lStrCode + "      strcpy(caQueryString,"" SET "");" + vbCrLf

        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")

            If lIntCNT = lIntTtlCNT Then
                lStrCode = lStrCode + "      strcat(caQueryString,""" + lStrColName + "=:n" + lIntCNT.ToString.Trim + """);" + vbCrLf
            Else
                lStrCode = lStrCode + "      strcat(caQueryString,""" + lStrColName + "=:n" + lIntCNT.ToString.Trim + ","");" + vbCrLf
            End If
        End While

        If lIntColCount > 0 Then   'if table has primay key
            lStrCode = lStrCode + lStrWhereLst
        End If

        lStrCode = lStrCode + "" + vbCrLf
    lStrCode = lStrCode + "      EXEC SQL PREPARE Updt" + lStrTmpStrucName + "Query FROM :caQueryString;" + vbCrLf
        lStrCode = lStrCode + "      if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_TRC,Warn(sqlca.sqlcode),""%s""" + vbCrLf
    lStrCode = lStrCode + "          ,""Error in Prepare in Updt" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "        ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""" + vbCrLf
    lStrCode = lStrCode + "        ,""Exiting Updt" + lStrTmpStrucName + " after parsing"");" + vbCrLf
        lStrCode = lStrCode + "      #endif" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "    }" + vbCrLf

        lStrCode = lStrCode + "" + vbCrLf
    lStrCode = lStrCode + "  EXEC SQL EXECUTE Updt" + lStrTmpStrucName + "Query USING " + vbCrLf


        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")
            lStrTempColName = lStrColName
            mprGetColName(lStrTempColName)

            lStrTempColName = lStrTempColName.Replace("_", " ")
            lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
            lStrTempColName = lStrTempColName.Replace(" ", "")

            If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                lStrTemp2 = ""
            Else
                lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
            End If

            mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)
            'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
            mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)

            lStrColNulInd = mDrData("TAB_COL_NULL_INDC")


      lStrTempColName = ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName

            If lIntCNT <> lIntTtlCNT Then
                lStrCode = lStrCode + "            " + lStrTempColName + "," + vbCrLf
            Else
                lStrCode = lStrCode + "            " + lStrTempColName + ";" + vbCrLf
            End If

        End While

        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "  if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "    {" + vbCrLf
        lStrCode = lStrCode + "      ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""," + vbCrLf
    lStrCode = lStrCode + "                      ""Error Executing Updt" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "      return(sqlca.sqlcode) ;" + vbCrLf
        lStrCode = lStrCode + "    }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf

        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Updt", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)

        '----------------------------------------------------------------------------------------------------------------
        'Update End
        '----------------------------------------------------------------------------------------------------------------


        '3-------------------------------------------------------------------------------------------------
        '   Gen Fetch Part
        '1-------------------------------------------------------------------------------------------------

    mprGenPartHead("Ftch", lStrTmpStrucName, lStrCode)

        '
    lStrCode = lStrCode + "RETURN_VALUE Ftch" + lStrTmpStrucName + "DY" + "(BOOL bPrpre," + vbCrLf + " " + lStrTmpTableName + " * p" + lStrTmpStrucName + ")" + vbCrLf

    mprGenPartInclude("Ftch", lStrTmpStrucName, lStrCode)

        '2-------------------------------------------------------------------------------------------------------------------
        'Gen Select Part
        lStrCode = lStrCode + "      strcpy(caQueryString,"" SELECT "");" + vbCrLf

        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")

            If lIntCNT = lIntTtlCNT Then
                lStrCode = lStrCode + "      strcat(caQueryString,""" + lStrColName + """);" + vbCrLf
            Else
                lStrCode = lStrCode + "      strcat(caQueryString,""" + lStrColName + ","");" + vbCrLf
            End If
        End While
        lStrCode = lStrCode + "      strcat(caQueryString,"" FROM "");" + vbCrLf
        lStrCode = lStrCode + "      strcat(caQueryString,"" TableName[" + mStrTableName + "-1]"");" + vbCrLf
        If lIntColCount > 0 Then   'if table has primay key
            lStrCode = lStrCode + lStrWhereLst
        End If

        lStrCode = lStrCode + "" + vbCrLf
    lStrCode = lStrCode + "      EXEC SQL PREPARE Ftch" + lStrTmpStrucName + "Query FROM :caQueryString;" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_TRC,Warn(sqlca.sqlcode),""%s""" + vbCrLf
    lStrCode = lStrCode + "          ,""Error in Prepare in Ftch" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf

    ' Nithya check

    lStrCode = lStrCode + "    EXEC SQL DECLARE SELECT_" + mStrTableName + " CURSOR FOR Ftch" + lStrTmpStrucName + "Query;" + vbCrLf

    lStrCode = lStrCode + "      if(sqlca.sqlcode)" + vbCrLf
    lStrCode = lStrCode + "        {" + vbCrLf
    lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_TRC,Warn(sqlca.sqlcode),""%s""" + vbCrLf
    lStrCode = lStrCode + "          ,""Error in Declare in Ftch" + lStrTmpStrucName + """);" + vbCrLf
    lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
    lStrCode = lStrCode + "        }" + vbCrLf
    lStrCode = lStrCode + "" + vbCrLf

        lStrCode = lStrCode + "      #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "        ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""" + vbCrLf
    lStrCode = lStrCode + "        ,""Exiting Ftch" + lStrTmpStrucName + " after parsing"");" + vbCrLf
        lStrCode = lStrCode + "      #endif" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "    }" + vbCrLf

        lStrCode = lStrCode + "" + vbCrLf

    'Nithya check

        If lIntColCount > 0 Then   ' If table has primary key
            lStrCode = lStrCode + "  EXEC SQL OPEN SELECT_" + mStrTableName + vbCrLf

            'Add key columns
            mprGetTableCols()
            lIntCNT = 0
            While mDrData.Read

                lStrColName = mDrData("TAB_COL_NAME")

                lStrTempColName = lStrColName
                mprGetColName(lStrTempColName)

                lStrTempColName = lStrTempColName.Replace("_", " ")
                lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
                lStrTempColName = lStrTempColName.Replace(" ", "")

                If mDrData.IsDBNull(4) Then
                    lStrColPrm = ""
                Else
                    lStrColPrm = mDrData("TTL_INDX_TYPE")
                End If

                If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                    lStrTemp2 = ""
                Else
                    lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
                End If

                mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)
                'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
                mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)


                If Trim(lStrColPrm) = "P" Then
                    'here need to handle
                    lIntCNT = lIntCNT + 1
                    If lIntCNT = 1 Then
            lStrCode = lStrCode + "           USING " + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
                    Else
            lStrCode = lStrCode + "                 " + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
                    End If

                    If lIntCNT <> lIntColCount Then
                        lStrCode = lStrCode + "," + vbCrLf
                    Else
                        lStrCode = lStrCode + ";" + vbCrLf
                    End If

                End If

            End While                                               'above ok , need test, 'where' has some problem ?
        Else
            lStrCode = lStrCode + "  EXEC SQL OPEN SELECT_" + mStrTableName + " ;" + vbCrLf
        End If

        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_DB,Warn(sqlca.sqlcode),""%s""" + vbCrLf
        lStrCode = lStrCode + "          ,""Error in opening cursor SELECT_" + mStrTableName + "."");" + vbCrLf
        lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf

        lStrCode = lStrCode + "  EXEC SQL FETCH SELECT_" + mStrTableName + vbCrLf


        mprGetTableCols()
        lIntCNT = 0
        While mDrData.Read
            lIntCNT = lIntCNT + 1

            lStrColName = mDrData("TAB_COL_NAME")
            lStrTempColName = lStrColName
            mprGetColName(lStrTempColName)

            lStrTempColName = lStrTempColName.Replace("_", " ")
            lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
            lStrTempColName = lStrTempColName.Replace(" ", "")

            If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                lStrTemp2 = ""
            Else
                lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
            End If

            mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)

            'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
            mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)

            lStrColNulInd = mDrData("TAB_COL_NULL_INDC")


      lStrTempColName = ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName

            If lIntCNT <> lIntTtlCNT Then
                If lIntCNT = 1 Then
                    lStrCode = lStrCode + "     INTO " + lStrTempColName + "," + vbCrLf
                Else
                    lStrCode = lStrCode + "          " + lStrTempColName + "," + vbCrLf
                End If

            Else
                lStrCode = lStrCode + "          " + lStrTempColName + ";" + vbCrLf
            End If

        End While

        lStrCode = lStrCode + "" + vbCrLf

        lStrCode = lStrCode + "      if(sqlca.sqlcode && abs(sqlca.sqlcode) NOT_EQUAL RECORD_NOT_FOUND)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_DB,Warn(sqlca.sqlcode),""%s""" + vbCrLf
        lStrCode = lStrCode + "          ,""Error in fetching cursor SELECT_" + mStrTableName + "."");" + vbCrLf
        lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf


        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Ftch", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)

        '----------------------------------------------------------------------------------------------------------------
        'Select End
        '----------------------------------------------------------------------------------------------------------------





        '3-------------------------------------------------------------------------------------------------
        '   Gen Delete Part
        '1-------------------------------------------------------------------------------------------------
    mprGenPartHead("Dlte", lStrTmpStrucName, lStrCode)

        '
    lStrCode = lStrCode + "RETURN_VALUE Dlte" + lStrTmpStrucName + "DY" + "(BOOL bPrpre," + vbCrLf + " " + lStrTmpTableName + " * p" + lStrTmpStrucName + ")" + vbCrLf

    mprGenPartInclude("Dlte", lStrTmpStrucName, lStrCode)

        lStrCode = lStrCode + "      strcat(caQueryString,"" DELETE FROM "");" + vbCrLf
        lStrCode = lStrCode + "      strcat(caQueryString,"" TableName[" + mStrTableName + "-1]"");" + vbCrLf
        If lIntColCount > 0 Then   'if table has primay key
            lStrCode = lStrCode + lStrWhereLst
        End If

        lStrCode = lStrCode + "" + vbCrLf
    lStrCode = lStrCode + "      EXEC SQL PREPARE Dlte" + lStrTmpStrucName + "Query FROM :caQueryString ;" + vbCrLf
        lStrCode = lStrCode + "      if(sqlca.sqlcode NOT_EQUAL 0)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,Warn(sqlca.sqlcode),""%s""," + vbCrLf
    lStrCode = lStrCode + "                 "" ERROR PREPARE Dlte" + lStrTmpStrucName + "Query"");" + vbCrLf
        lStrCode = lStrCode + "          return (sqlca.sqlcode) ;" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf

        lStrCode = lStrCode + "      #ifdef DBG" + vbCrLf
        lStrCode = lStrCode + "        ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""" + vbCrLf
    lStrCode = lStrCode + "        ,""Exiting Dlte" + lStrTmpStrucName + " after parsing"");" + vbCrLf
        lStrCode = lStrCode + "      #endif" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        lStrCode = lStrCode + "      return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "    }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf
        'above ok, need test

        '2-------------------------------------------------------------------------------------------------------------------
        If lIntColCount > 0 Then   ' If table has primary key
      lStrCode = lStrCode + "  EXEC SQL EXECUTE Dlte" + lStrTmpStrucName + "Query" + vbCrLf

            'Add key columns
            mprGetTableCols()
            lIntCNT = 0
            While mDrData.Read

                lStrColName = mDrData("TAB_COL_NAME")

                lStrTempColName = lStrColName
                mprGetColName(lStrTempColName)

                lStrTempColName = lStrTempColName.Replace("_", " ")
                lStrTempColName = StrConv(lStrTempColName, VbStrConv.ProperCase)
                lStrTempColName = lStrTempColName.Replace(" ", "")

                If mDrData.IsDBNull(4) Then
                    lStrColPrm = ""
                Else
                    lStrColPrm = mDrData("TTL_INDX_TYPE")
                End If

                If IsDBNull(mDrData("TDCM_BACK_END_DATA_TYPE")) Then
                    lStrTemp2 = ""
                Else
                    lStrTemp2 = mDrData("TDCM_BACK_END_DATA_TYPE")
                End If

                mfnRtrnVarPrfix(lStrTemp2, lStrVrblSuffix)
                'mfnRtrnVarPrfix(mDrData("TDCM_BACK_END_DATA_TYPE"), lStrVrblSuffix)
                mfnRtrnPrnPrfix(lStrVrblSuffix, lStrPrnParm)


                If Trim(lStrColPrm) = "P" Then
                    'here need to handle
                    lIntCNT = lIntCNT + 1
                    If lIntCNT = 1 Then
            lStrCode = lStrCode + "           USING " + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
                    Else
            lStrCode = lStrCode + "                 " + ":p" + lStrTmpStrucName + "->" + lStrVrblSuffix + lStrTempColName
                    End If

                    If lIntCNT <> lIntColCount Then
                        lStrCode = lStrCode + "," + vbCrLf
                    Else
                        lStrCode = lStrCode + ";" + vbCrLf
                    End If

                End If

            End While                                               'above ok , need test, 'where' has some problem ?
        Else
      lStrCode = lStrCode + "  EXEC SQL EXECUTE Dlte" + lStrTmpStrucName + "Query;" + vbCrLf
        End If

        lStrCode = lStrCode + "      if(sqlca.sqlcode)" + vbCrLf
        lStrCode = lStrCode + "        {" + vbCrLf
        lStrCode = lStrCode + "          ProcessEventLog(TRC_DBG,ERR_TRC,Warn(sqlca.sqlcode),""%s""" + vbCrLf
    lStrCode = lStrCode + "          ,""Error in Prepare in Dlte" + lStrTmpStrucName + """);" + vbCrLf
        lStrCode = lStrCode + "          return(sqlca.sqlcode);" + vbCrLf
        lStrCode = lStrCode + "        }" + vbCrLf
        lStrCode = lStrCode + "" + vbCrLf

        '3----------------------------------------------------------------
        '
    mprGenPartFoot("Dlte", lStrTmpStrucName, lStrCode, lStrPrnLst, lStrColLst)

        '----------------------------------------------------------------------------------------------------------------
        'Delete End
        '----------------------------------------------------------------------------------------------------------------


        'Save File
    clsobjFileGen.mprGnrtProCBodyFile(lStrCode, lStrTmpStrucName + "DY", "ProC Files")

    End Function



    Private Function mprGetColName(ByRef aStrColName As String)
        Dim lIntPos As Integer

        lIntPos = aStrColName.IndexOf("_")
        aStrColName = aStrColName.Substring(lIntPos + 1)

    End Function


    Private Function mFnCheckTableValidity(ByVal aStrTableName As String) As Boolean

        Dim lStrQuery As String = ""
        Dim lIntCount As Integer



        lStrQuery = "select count(*) as CNT from TRDX_TABLES_MSTR where TABM_TABLE_NAME = UPPER('"
        lStrQuery = lStrQuery + aStrTableName + "')"

        mCmdData = New OracleCommand(lStrQuery, con)
        lIntCount = mCmdData.ExecuteScalar

        If lIntCount = 0 Then
            Return False
        Else
            Return True
        End If

    End Function




    Private Sub mprGetTableCols()

        Dim lStrQuery As String = ""



        lStrQuery = "select distinct TAB_COL_NAME , TAB_COL_SEQ_NO, TAB_COL_NULL_INDC,"
        lStrQuery = lStrQuery + "                TDCM_BACK_END_DATA_TYPE , TTL_INDX_TYPE, TTL_COLM_SEQC_NMBR"
        lStrQuery = lStrQuery + " from TRDX_TABLE_COLUMNS left join TRDX_APLCTN_VRBLS on TAB_VRBL_SERIAL_NMBR = TAV_SERIAL_NMBR "
        lStrQuery = lStrQuery + "      left join TRDX_DATA_CATEGORY_MSTR on TAV_DATA_CTGRY = TDCM_DATA_CTGRY "
    lStrQuery = lStrQuery + "       left join TRDX_TABL_INDX on TAB_COL_ID = TTL_COLM_IDXX and TTL_TABL_IDXX =  TAB_TABLE_ID AND TTL_INDX_TYPE = 'P'"
        lStrQuery = lStrQuery + " where TAB_TABLE_ID = ( select distinct TABM_TABLE_ID "
        lStrQuery = lStrQuery + "                                  from TRDX_TABLES_MSTR "
        lStrQuery = lStrQuery + "                                 where TABM_TABLE_NAME = upper('" + mStrTableName + "') ) "
        lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO ASC"

        mCmdData = New OracleCommand(lStrQuery, con)
        mDrData = mCmdData.ExecuteReader

    End Sub

    Private Sub mprGenPartHead(ByVal aOpr As String, ByVal aStrTableName As String, ByRef aStrCode As String)

        aStrCode = aStrCode + "/**************************************************************************" + vbCrLf
        aStrCode = aStrCode + "***************************************************************************" + vbCrLf
    aStrCode = aStrCode + "**   Function Name : " + aOpr + aStrTableName + "DY" + Space(46 - aStrTableName.Length) + "**" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Input Params  : bPrpre, p" + aStrTableName + Space(43 - aStrTableName.Length) + "**" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
    aStrCode = aStrCode + "**   Output Params : p" + aStrTableName + Space(51 - aStrTableName.Length) + "**" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
    aStrCode = aStrCode + "**   Description   : " + aOpr + " service                                        **" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Return Value  : returns 0 on success and sqlcode on failure         **" + vbCrLf
        aStrCode = aStrCode + "**                                                                       **" + vbCrLf
        aStrCode = aStrCode + "**   Mod History   :                                                     **" + vbCrLf
        aStrCode = aStrCode + "***************************************************************************" + vbCrLf
        aStrCode = aStrCode + "**************************************************************************/" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf

    End Sub

    Private Sub mprGenPartInclude(ByVal aOpr As String, ByVal aStrTableName As String, ByRef aStrCode As String)
        aStrCode = aStrCode + "{" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "  #ifdef DBG" + vbCrLf
        aStrCode = aStrCode + "    ProcessEventLog(TRC_DBG,ERR_DB,0,""%s""," + vbCrLf
        aStrCode = aStrCode + "                 "" Entered " + aOpr + aStrTableName + """);" + vbCrLf
        aStrCode = aStrCode + "  #endif	" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf                                'above ok, need test

        aStrCode = aStrCode + "  EXEC SQL BEGIN DECLARE SECTION  ;" + vbCrLf
    aStrCode = aStrCode + "    QUERY_STRING  caQueryString[QUERY_STRING_LNGTH];" + vbCrLf
        aStrCode = aStrCode + "  EXEC SQL END DECLARE SECTION    ; " + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf

        aStrCode = aStrCode + "  sqlca.sqlcode = 0 ;" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf

        aStrCode = aStrCode + "  if(bPrpre)" + vbCrLf
        aStrCode = aStrCode + "    {" + vbCrLf
    End Sub

    Private Sub mprGenPartFoot(ByVal aOpr As String, ByVal aStrTableName As String, ByRef aStrCode As String, ByVal aStrPrnlst As String, ByVal aStrColLst As String)

        aStrCode = aStrCode + "  #ifdef DBG" + vbCrLf
        aStrCode = aStrCode + "    ProcessEventLog(TRC_DBG,ERR_TRC,sqlca.sqlcode,""%s""," + vbCrLf + "        "" Exit " + aOpr + aStrTableName + """);" + vbCrLf
        aStrCode = aStrCode + "  #endif" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "  return sqlca.sqlcode;" + vbCrLf
        aStrCode = aStrCode + "" + vbCrLf
        aStrCode = aStrCode + "}" + vbCrLf                   'above ok, need test

    End Sub




    Private Function mfnRtrnVarPrfix(ByVal aDataType As String _
                                   , ByRef aReturnType As String)

        Dim lPassResult As String = ""

        Select Case LCase((aDataType))

            Case ("double")
                lPassResult = "d"
            Case ("char array")
                lPassResult = "ca"
            Case ("long")
                lPassResult = "l"
            Case ("int")
                lPassResult = "i"
            Case ("date")
                lPassResult = "dt"
            Case Else
                lPassResult = ""
        End Select

        aReturnType = lPassResult

    End Function

    Private Function mfnRtrnPrnPrfix(ByVal aPrefix As String _
                                   , ByRef aReturnType As String)

        Dim lPassResult As String = ""

        Select Case LCase((aPrefix))

            Case ("c")
                lPassResult = "%c"
            Case ("d")
                lPassResult = "%d"
            Case ("ca")
                lPassResult = "%s"
            Case ("i")
                lPassResult = "%d"
            Case ("l")
                lPassResult = "%l"
            Case ("dt")
                lPassResult = "%l"
            Case ("s")
                lPassResult = "%h"
            Case ("ll")
                lPassResult = "%l"
            Case ("f")
                lPassResult = "%f"
            Case ("ld")
                lPassResult = "%f"
            Case Else
                lPassResult = ""
        End Select

        aReturnType = lPassResult

    End Function

End Class

